-- 1.创建项目成本核算—直接资产成本明细表自增序列
declare v_max_id int;
        -- 定义sql语句
        v_sql nvarchar(5000);
begin
	-- 删除序列
	set v_sql = 'drop sequence if exists cost.seq_pro_cost_cal_detail_direct_dev';
    execute immediate v_sql;

    -- 创建序列
    set v_sql = 'create sequence cost.seq_pro_cost_cal_detail_direct_dev increment by 1 maxvalue 999999999999 minvalue 1';
	execute immediate v_sql;

	select nvl(max(id) + 1, 1) into v_max_id from cost.pro_cost_cal_detail_direct_dev where 1 = 1;

    -- 更改步长(increment)为一个你想让序列增加到的值
    set v_sql = concat('alter sequence cost.seq_pro_cost_cal_detail_direct_dev increment by ', v_max_id);
    execute immediate v_sql;

    -- 执行一下查询序列的语句
    set v_sql = 'select cost.seq_pro_cost_cal_detail_direct_dev.nextval from dual';
    execute immediate v_sql;

    -- 修改为原来的步长
    set v_sql = 'alter sequence cost.seq_pro_cost_cal_detail_direct_dev increment by 1';
    execute immediate v_sql;
end;

-- 2.项目成本核算—直接资产成本明细表重命名
alter table pro_cost_cal_detail_direct_dev rename to pro_cost_cal_detail_direct_dev_old;

-- 3.创建项目成本核算—直接资产成本明细分区表
create table cost.pro_cost_cal_detail_direct_dev(
    id int default cost.seq_pro_cost_cal_detail_direct_dev.nextval not null,
	param_flag_id int null,
	cal_res_id int null,
	check_scheme_id int null,
	organization_id int null,
	hospital_structure_id int null,
	serve_item_code nvarchar(600) null,
	number decimal(28,12) null,
	price decimal(28,12) null,
	work_id int null,
	dev_id int null,
	dev_code nvarchar(600) null,
	dev_name nvarchar(1200) null,
	dev_work_min decimal(28,12) null,
	dev_work_ratio decimal(28,12) null,
	dev_work_cost_dep_per_min decimal(28,12) null,
	dev_work_cost_main_per_min decimal(28,12) null,
	dev_work_cal_cost_dep decimal(28,12) null,
	dev_work_cal_cost_main decimal(28,12) null,
	dev_work_cal_cost decimal(28,12) null,
	medical_cost_type int null,
	create_user_code nvarchar(360) null,
	create_time timestamp null,
	update_user_code nvarchar(360) null,
	update_time timestamp NULL,
	delete_flag tinyint null,
	sink_cost_dep_per_min decimal(28,12) null,
	sink_cost decimal(28,12) null,
	cost_item_code varchar(240) null,
	calculate_method int null
) PARTITION BY LIST (check_scheme_id)
  (PARTITION p_cddd_1 VALUES(1) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_2 VALUES(2) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_3 VALUES(3) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_4 VALUES(4) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_5 VALUES(5) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_6 VALUES(6) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_7 VALUES(7) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_8 VALUES(8) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_9 VALUES(9) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_10 VALUES(10) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_11 VALUES(11) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_12 VALUES(12) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_13 VALUES(13) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_14 VALUES(14) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_15 VALUES(15) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_16 VALUES(16) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_17 VALUES(17) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_18 VALUES(18) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_19 VALUES(19) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_20 VALUES(20) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_21 VALUES(21) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_22 VALUES(22) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_23 VALUES(23) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_24 VALUES(24) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_25 VALUES(25) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_26 VALUES(26) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_27 VALUES(27) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_28 VALUES(28) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_29 VALUES(29) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_30 VALUES(30) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_31 VALUES(31) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_32 VALUES(32) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_33 VALUES(33) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_34 VALUES(34) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_35 VALUES(35) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_36 VALUES(36) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_37 VALUES(37) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_38 VALUES(38) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_39 VALUES(39) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_40 VALUES(40) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_41 VALUES(41) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_42 VALUES(42) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_43 VALUES(43) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_44 VALUES(44) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_45 VALUES(45) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_46 VALUES(46) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_47 VALUES(47) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_48 VALUES(48) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_49 VALUES(49) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_50 VALUES(50) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_51 VALUES(51) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_52 VALUES(52) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_53 VALUES(53) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_54 VALUES(54) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_55 VALUES(55) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_56 VALUES(56) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_57 VALUES(57) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_58 VALUES(58) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_59 VALUES(59) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_60 VALUES(60) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_61 VALUES(61) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_62 VALUES(62) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_63 VALUES(63) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_64 VALUES(64) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_65 VALUES(65) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_66 VALUES(66) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_67 VALUES(67) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_68 VALUES(68) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_69 VALUES(69) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_70 VALUES(70) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_71 VALUES(71) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_72 VALUES(72) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_73 VALUES(73) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_74 VALUES(74) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_75 VALUES(75) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_76 VALUES(76) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_77 VALUES(77) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_78 VALUES(78) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_79 VALUES(79) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_80 VALUES(80) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_81 VALUES(81) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_82 VALUES(82) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_83 VALUES(83) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_84 VALUES(84) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_85 VALUES(85) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_86 VALUES(86) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_87 VALUES(87) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_88 VALUES(88) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_89 VALUES(89) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_90 VALUES(90) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_91 VALUES(91) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_92 VALUES(92) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_93 VALUES(93) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_94 VALUES(94) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_95 VALUES(95) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_96 VALUES(96) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_97 VALUES(97) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_98 VALUES(98) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_99 VALUES(99) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_100 VALUES(100) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_101 VALUES(101) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_102 VALUES(102) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_103 VALUES(103) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_104 VALUES(104) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_105 VALUES(105) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_106 VALUES(106) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_107 VALUES(107) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_108 VALUES(108) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_109 VALUES(109) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_110 VALUES(110) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_111 VALUES(111) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_112 VALUES(112) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_113 VALUES(113) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_114 VALUES(114) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_115 VALUES(115) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_116 VALUES(116) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_117 VALUES(117) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_118 VALUES(118) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_119 VALUES(119) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_120 VALUES(120) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_121 VALUES(121) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_122 VALUES(122) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_123 VALUES(123) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_124 VALUES(124) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_125 VALUES(125) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_126 VALUES(126) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_127 VALUES(127) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_128 VALUES(128) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_129 VALUES(129) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_130 VALUES(130) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_131 VALUES(131) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_132 VALUES(132) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_133 VALUES(133) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_134 VALUES(134) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_135 VALUES(135) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_136 VALUES(136) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_137 VALUES(137) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_138 VALUES(138) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_139 VALUES(139) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_140 VALUES(140) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_141 VALUES(141) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_142 VALUES(142) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_143 VALUES(143) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_144 VALUES(144) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_145 VALUES(145) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_146 VALUES(146) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_147 VALUES(147) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_148 VALUES(148) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_149 VALUES(149) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_cddd_150 VALUES(150) STORAGE(ON "MAIN", CLUSTERBTR)
) STORAGE(ON "MAIN", CLUSTERBTR);

-- 4.增加注释
comment on table cost.pro_cost_cal_detail_direct_dev is '项目成本核算—直接资产成本明细表';
comment on column cost.pro_cost_cal_detail_direct_dev.id is '主键id';
comment on column cost.pro_cost_cal_detail_direct_dev.param_flag_id is '核算参数采集完成标志表id';
comment on column cost.pro_cost_cal_detail_direct_dev.cal_res_id is '项目成本核算主表id';
comment on column cost.pro_cost_cal_detail_direct_dev.check_scheme_id is '核算方案主表id';
comment on column cost.pro_cost_cal_detail_direct_dev.organization_id is '科室id';
comment on column cost.pro_cost_cal_detail_direct_dev.hospital_structure_id is '院区id';
comment on column cost.pro_cost_cal_detail_direct_dev.serve_item_code is '服务项目编码';
comment on column cost.pro_cost_cal_detail_direct_dev.number is '服务项目数量';
comment on column cost.pro_cost_cal_detail_direct_dev.price is '服务项目单价';
comment on column cost.pro_cost_cal_detail_direct_dev.work_id is '作业id';
comment on column cost.pro_cost_cal_detail_direct_dev.dev_id is '设备id';
comment on column cost.pro_cost_cal_detail_direct_dev.dev_code is '设备code';
comment on column cost.pro_cost_cal_detail_direct_dev.dev_name is '设备名称';
comment on column cost.pro_cost_cal_detail_direct_dev.dev_work_min is '项目使用分钟数';
comment on column cost.pro_cost_cal_detail_direct_dev.dev_work_ratio is '工作量比例';
comment on column cost.pro_cost_cal_detail_direct_dev.dev_work_cost_dep_per_min is '每分钟折旧成本';
comment on column cost.pro_cost_cal_detail_direct_dev.dev_work_cost_main_per_min is '每分钟维保成本';
comment on column cost.pro_cost_cal_detail_direct_dev.dev_work_cal_cost_dep is '计算后折旧成本';
comment on column cost.pro_cost_cal_detail_direct_dev.dev_work_cal_cost_main is '计算后维保成本';
comment on column cost.pro_cost_cal_detail_direct_dev.dev_work_cal_cost is '计算后成本';
comment on column cost.pro_cost_cal_detail_direct_dev.medical_cost_type is '成本维度';
comment on column cost.pro_cost_cal_detail_direct_dev.create_user_code is '创建人编码';
comment on column cost.pro_cost_cal_detail_direct_dev.create_time is '创建时间';
comment on column cost.pro_cost_cal_detail_direct_dev.update_user_code is '修改人编码';
comment on column cost.pro_cost_cal_detail_direct_dev.update_time is '修改时间';
comment on column cost.pro_cost_cal_detail_direct_dev.delete_flag is '删除标记:0.存在,1.已删除';
comment on column cost.pro_cost_cal_detail_direct_dev.sink_cost_dep_per_min is '每分钟沉没成本';
comment on column cost.pro_cost_cal_detail_direct_dev.sink_cost is '单位沉没成本';
comment on column cost.pro_cost_cal_detail_direct_dev.cost_item_code is '成本项目编码';
comment on column cost.pro_cost_cal_detail_direct_dev.calculate_method is '计算方法:1303.当量法,1801.时间驱动作业成本法';

-- 4.删除和重建索引
declare v_idx_sql nvarchar(5000); -- 定义sql语句
begin
	-- 删除索引idx_pro_cost_cal_detail_direct_dev
	set v_idx_sql = 'drop index if exists idx_pro_cost_cal_detail_direct_dev';
    execute immediate v_idx_sql;

    -- 创建索引idx_pro_cost_cal_detail_direct_dev
    set v_idx_sql = 'create index idx_pro_cost_cal_detail_direct_dev on cost.pro_cost_cal_detail_direct_dev(id)';
    execute immediate v_idx_sql;

    -- 删除索引idx_non_clu_pro_cost_cal_detail_direct_dev
    set v_idx_sql = 'drop index if exists idx_non_clu_pro_cost_cal_detail_direct_dev';
    execute immediate v_idx_sql;

    -- 创建索引idx_non_clu_pro_cost_cal_detail_direct_dev
    set v_idx_sql = 'create index idx_non_clu_pro_cost_cal_detail_direct_dev on cost.pro_cost_cal_detail_direct_dev(check_scheme_id, organization_id)';
    execute immediate v_idx_sql;
end;

-- 5.项目成本核算—直接资产成本明细表数据迁移
insert into cost.pro_cost_cal_detail_direct_dev(
    id, param_flag_id, cal_res_id, check_scheme_id,
    organization_id, hospital_structure_id, serve_item_code, number,
    price, work_id, dev_id, dev_code,
    dev_name, dev_work_min, dev_work_ratio, dev_work_cost_dep_per_min,
    dev_work_cost_main_per_min, dev_work_cal_cost_dep, dev_work_cal_cost_main, dev_work_cal_cost,
    medical_cost_type, create_user_code, create_time, update_user_code,
    update_time, delete_flag, sink_cost_dep_per_min, sink_cost,
    cost_item_code, calculate_method
)
select id, param_flag_id, cal_res_id, check_scheme_id,
       organization_id, hospital_structure_id, serve_item_code, number,
       price, work_id, dev_id, dev_code,
       dev_name, dev_work_min, dev_work_ratio, dev_work_cost_dep_per_min,
       dev_work_cost_main_per_min, dev_work_cal_cost_dep, dev_work_cal_cost_main, dev_work_cal_cost,
       medical_cost_type, create_user_code, create_time, update_user_code,
       update_time, delete_flag, sink_cost_dep_per_min, sink_cost,
       cost_item_code, calculate_method
from cost.pro_cost_cal_detail_direct_dev_old;
commit;

-- 6.删除原项目成本核算—直接资产成本明细表
drop table if exists cost.pro_cost_cal_detail_direct_dev_old;

-- 7.查看表分区是否存在
select count(1)
        from user_tab_partitions
        where lower(table_name) = 'pro_cost_cal_detail_direct_dev'
          and lower(partition_name) = 'p_cddd_100';

select count(1) from user_tab_partitions where lower(table_name) = 'pro_cost_cal_detail_direct_dev' and lower(partition_name) = 'p_cddd_86'；

-- 8.增加分区
ALTER TABLE cost.pro_cost_cal_detail_direct_dev ADD PARTITION p_cddd_100 VALUES(100);

alter table cost.pro_cost_cal_detail_direct_dev add partition p_cddd_86 values(86);

-- 9.删除分区
    使用 drop partition 可将分区的定义连同数据一起删除，示例语句如下：
    alter table cost.pro_cost_cal_detail_direct_dev drop partition p_cddd_100;
    使用 truncate partition 仅删除分区的数据，示例语句如下：
    alter table cost.pro_cost_cal_detail_direct_dev truncate partition p_cddd_100;
	
-- 10.指定分区写入数据
insert into cost.pro_cost_cal_detail_direct_dev partition(p_cddd_77) 
      (cal_res_id, check_scheme_id, organization_id, serve_item_code,  
	   number, price, work_id, dev_id, dev_code,  
	   dev_name, dev_work_min, dev_work_ratio, 
	   dev_work_cost_dep_per_min,  dev_work_cost_main_per_min, 
	   dev_work_cal_cost_dep, dev_work_cal_cost_main, dev_work_cal_cost,  
	   sink_cost, cost_item_code, medical_cost_type, sink_cost_dep_per_min,  
	   calculate_method, hospital_structure_id, create_time, update_time,  delete_flag  ) 
values (?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?,?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?,?)

-- 11.指定分区查询数据
select
		ddd.check_scheme_id,
		ddd.organization_id,
		ddd.serve_item_code,
		ddd.cost_item_code,
		ddd.medical_cost_type,
		ddd.calculate_method,
		sum(ddd.dev_work_cal_cost * ddd.number)
	from
		cost.pro_cost_cal_detail_direct_dev partition(p_cddd_86) ddd
	where
		ddd.check_scheme_id = 86
		and ddd.calculate_method in ( 1801 , 1803 )
	group by
		ddd.check_scheme_id,
		ddd.organization_id,
		ddd.serve_item_code,
		ddd.cost_item_code,
		ddd.medical_cost_type,
		ddd.calculate_method;
	
-- 12.关联删除(指定分区)
delete
from cost.pro_cost_cal_detail_direct_dev partition(p_cddd_77) ddd
where ddd.check_scheme_id = 77
  and exists(
	  select 1 from cost.t_res_all_1802_77 t1
	  where t1.id = ddd.cal_res_id 
  )

-- 13.关联修改(指定分区)
update cost.pro_cost_cal_detail_direct_dev partition(p_cddd_77) 
       set cost.pro_cost_cal_detail_direct_dev.number = t1.number
from cost.pro_cost_cal_detail_direct_dev partition(p_cddd_77)
     join cost.t_res_some_1802_77 t1 on t1.id = cost.pro_cost_cal_detail_direct_dev.cal_res_id
where cost.pro_cost_cal_detail_direct_dev.check_scheme_id = 77;